package com.mybatis.demo.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class JDBCUtils {

	public static Connection getConn() throws Exception {
		//1.注册驱动
		Class.forName("com.mysql.jdbc.Driver");
		//2.创建连接
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/nhdjtest?useUnicode=true&&characterEncoding=UTF-8", "root", "123");
		return conn;
	}

	public static void main(String[] args) throws Exception {
		List<String> strings = Arrays.asList("b", "a", "d", "c");
		List<String> sort = sort(strings, true);
	}
	public static void test1()throws Exception{
		Connection connection = getConn();
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery("select * from user where emp_id='000002'");
		ResultSetMetaData metaData = resultSet.getMetaData();
		int count = metaData.getColumnCount();
		//遍历字段名和类型
		for (int i = 0; i < count; i++) {
			String name = metaData.getColumnName(i+1);
			String type = metaData.getColumnTypeName(i+1);
			System.out.println(name+":"+type);
		}
		while (resultSet.next()){
			String emp_id = resultSet.getString("emp_id");
			String name = resultSet.getString("name");
			String password = resultSet.getString("password");
			System.out.println("emp_id："+emp_id);
			System.out.println("name："+name);
			System.out.println("password："+password);

		}
	}
	public static List<String> sort(List<String> param,Boolean isDesc ) throws Exception {
		StringBuilder stringBuilder = new StringBuilder("SELECT item from (");
		stringBuilder.append( "SELECT '"+param.get(0)+"'as item" );
		for (int i = 1; i < param.size(); i++) {
			stringBuilder.append( " union all " );
			stringBuilder.append( "SELECT '"+param.get(i)+"' " );
		}
		stringBuilder.append( ") as tmp  order by item  " );
		if(isDesc){
			stringBuilder.append( " desc;" );
		}
		Connection connection = getConn();
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(stringBuilder.toString());
		List<String> result = new ArrayList<>();
		while (resultSet.next()){
			result.add(resultSet.getString("item"));
		}
		System.out.println(result);
		return result;
	}

	public static void test()throws Exception{
		System.out.println("执行批量===开始");
		Connection connection = getConn();
		Statement statement = connection.createStatement();
		for (int j = 0; j < 10; j++) {
			StringBuilder sql=new StringBuilder("INSERT into user_role (emp_id,department_role_id) VALUES ");
			for (int i = 0; i < 1000000; i++) {
				if(i==999999){
					sql.append( "("+i+","+i+")");
				}else {
					sql.append( "("+i+","+i+"),");
				}
			}
			statement.addBatch(sql.toString());
		}

		statement.executeBatch();
		System.out.println("执行批量===结束");
	}
}

